SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create PROCEDURE [dbo].[SP_TDSUBR_PROY_Q01]
/*---------------------------------------------------------*/        
/*---- Empresa     : OFISIS S.A    ----*/        
/*---- Cliente     : OFISIS S.A.    ----*/        
/*---- Sistema     : Tesoreria     ----*/        
/*---- Modulo     : Empresa     ----*/        
/*---- Programa    : Flujo de Caja Proyectado, Eje ----*/        
/*---- Script     : TTARUB_FINA_Q01.SQL   ----*/        
/*---- Desarrollado por    : Carlos Marquez Escobar ----*/        
/*---- Fecha Creacion    : 05/01/2003   ----*/        
/*---- Base de Datos    : SQL SERVER   ----*/        
/*---- Version     : 8.0      ----*/        
/*---- Invoca a SP     :       ----*/        
/*---------------------------------------------------------*/        
/*-- Comentarios    :               
CREATE TABLE #TDSUBR_PROY (        
 CO_EMPR  Varchar(002) Null,        
 NU_ANNO  Int    Null,         
 NU_MESE  Int    Null,        
 CO_SUBR  Varchar(003) Null,        
 FE_PROY_EJEC Datetime  Null,         
     TI_REPO        Varchar(001) Null,        
 ST_DIGI_PROY Varchar(001) Null,        
 CO_MONE  Varchar(003) Null,         
 FE_DETA_DIGI Datetime  Null,         
 DE_SUBR  Varchar(100) Null,        
 IM_SALD  Numeric(16,4) Null,        
 IM_DIGI  Numeric(16,4) Null,        
 IM_EJEC  Numeric(16,4) Null)           
        
Create Table #TWDETA_PREJ(        
 CO_EMPR           Varchar(02)    Null,        
 FE_PREJ           Datetime       Null,        
 TI_REPO           Varchar(01)    Null,        
 CO_MONE_REPO  Varchar(03)    Null,        
 FE_DETA           Datetime       Null,        
 CO_PROV           Varchar(20)    Null,        
 CO_TIPO_DOCU    Varchar(03)    Null,        
 NU_DOCU_PROV  Varchar(20)    Null,        
 CO_SUBR           Varchar(03)    Null,        
 IM_ORIG           Numeric(16,4)  Null,        
 IM_CONV           Numeric(16,4)  Null)        
        
        
----*/        
/*---------------------------------------------------------*/        
/*---------------------------------------------------------*/        
/* drop proc SP_TDSUBR_PROY_Q01       ----*/         
/* grant all on SP_TDSUBR_PROY_Q01 to PUBLIC   ----*/        
/* SP_TDSUBR_PROY_Q01  '01', 'DOL', 'FTEEMEF', '2005-01-01', '2005-01-01', 'F' */        
/*---------------------------------------------------------*/        
(        
@ISCO_EMPR      TD_VC_020,        
@ISCO_MONE      TD_VC_003,        
@ISCO_MENU      TD_VC_008,        
@IDFE_PROY      TD_DT_001,        
@IDFE_PRIM_DIAS TD_DT_001,        
@ISTI_REPO          TD_VC_001        
)        
      
AS        
--- *** DECLARACION DE VARIABLES DE APOYO PARA EL POSTERIOR ALMACENAMIENTO EN LA TEMPORAL *** ---        
Declare        
 @CSCO_EMPR  TD_VC_020,        
 @CSCO_SUBR  TD_VC_003,        
 @CSDE_SUBR  TD_VC_100,        
        
 @CSCO_SUBR_PROY TD_VC_003,        
 @CDNU_DIA1  TD_DT_001,        
 @CDNU_DIA2  TD_DT_001,        
 @CSPL_CONT  TD_VC_020,        
        
 @VNIM_DIGI	TD_NU_016_004,


 @VSCO_SUBR  TD_VC_003,        
 @VNNU_ANNO  TD_IN_001,        
 @VNNU_MESE  TD_IN_001,        
 @VNNU_ACCI  TD_IN_001,        
 @VSCO_MONE_DEFA TD_VC_003,        
 @VSCO_MONE_EXTR TD_VC_003,        
 @VSCO_TIAU_CCOS TD_VC_001,        
 @VSCO_TIAU_CLIE TD_VC_001,        
 @VSCO_TIAU_PROV TD_VC_001,        
 @VSCO_TIAU_EMPL TD_VC_001,        
 @VSTI_CAMB  TD_VC_003,        
 @VSTI_ORIG  TD_VC_003,        
 @VNNU_DIAS  TD_IN_001,        
 @VNNU_DIAS_COUNT TD_IN_001,        
 @VDFE_DIAS_AUXI TD_DT_001,        
 @VDFE_DIUL_ANTE TD_DT_001,        
 @VDFE_DIAS_MESA TD_DT_001,        
        
 @IDFE_PROY_REAL TD_DT_001        
        
-------------------------------------------------------------------------------------------------        
-- borrar !!!!!!        
/*        
CREATE TABLE #TDSUBR_PROY (        
 CO_EMPR  Varchar(002) Null,        
 NU_ANNO  Int    Null,         
 NU_MESE  Int    Null,        
 CO_SUBR  Varchar(003) Null,        
 FE_PROY_EJEC Datetime  Null,         
 ST_DIGI_PROY Varchar(001) Null,        
 CO_MONE  Varchar(003) Null,         
 FE_DETA_DIGI Datetime  Null,         
 DE_SUBR  Varchar(100) Null,        
 IM_SALD  Numeric(16,4) Null,        
 IM_DIGI  Numeric(16,4) Null,        
 IM_EJEC  Numeric(16,4) Null,        
 TI_REPO  Varchar(1) Null)          
*/        
-------------------------------------------------------------------------------------------------        
/*        
CREATE TABLE #TWDETA_PREJ        
(        
CO_EMPR          Varchar(02)   Null,         
FE_PREJ          Datetime      Null,         
TI_REPO          Varchar(01)   N      
ull,         
CO_MONE_REPO     Varchar(03)   Null,         
FE_DETA          Datetime      Null,         
CO_PROV          Varchar(20)   Null,         
CO_TIPO_DOCU     Varchar(03)   Null,         
NU_DOCU_PROV     Varchar(20)   Null,        
CO_SUBR          Varchar(03)   Null,         
IM_ORIG          Numeric(16,4) Null,         
IM_CONV          Numeric(16,4) Null         
)        
*/        
-------------------------------------------------------------------------------------------------        
Begin        
 --- *** CREACION DE LA TABLA TEMPORAL EN DONDE SE ALMACENARAN LOS DATOS FINALES *** ---        
 CREATE TABLE #TDSUBR_PROY_Q01_1        
 (        
 CO_EMPR  Varchar(002) Null,        
 NU_ANNO  Int    Null,         
 NU_MESE  Int    Null,        
 CO_SUBR  Varchar(003) Null,        
 FE_PROY_EJEC Datetime  Null,         
 CO_MONE  Varchar(003) Null,         
 IM_ORIG  Numeric(16,4) Null,        
 IM_CONV  Numeric(16,4) Null,        
 ST_EJEC  Varchar(001) Null        
 )        
        
CREATE TABLE #TDSUBR_PROY_DIAS (        
 CO_EMPR  Varchar(002) Null,        
 NU_ANNO  Int    Null,         
 NU_MESE  Int    Null,        
 CO_SUBR  Varchar(003) Null,        
 FE_PROY_EJEC Datetime  Null,         
 CO_MONE  Varchar(003) Null,         
 FE_DETA_DIGI Datetime  Null,         
 DE_SUBR  Varchar(100) Null,        
 IM_DIGI  Numeric(16,4) Null,        
 IM_EJEC  Numeric(16,4) Null)        
        
-------------------------------------------------------------------------------------------        
--- *** OBTIENE MONEDA NACIONAL Y EXTRAJERA, TIPO DE CAMBIO x PANTALLA O PARAMETROS *** ---         
 Select  @VSCO_MONE_DEFA = T1.CO_MONE_DEFA, @VSCO_MONE_EXTR = T1.CO_MONE_EXTR,        
   @VSTI_CAMB = IsNull(T2.TI_CAMB,T1.TI_CAMB), @VSTI_ORIG = IsNull(T2.TI_ORIG, T1.TI_ORIG),        
   @VSCO_TIAU_PROV = T1.CO_TIAU_PROV,        
   @VSCO_TIAU_CLIE = T1.CO_TIAU_CLIE,        
   @VSCO_TIAU_CCOS = T1.CO_TIAU_CCOS,        
   @VSCO_TIAU_EMPL = T1.CO_TIAU_EMPL        
 From  TMPARA_TESO T1        
 Left Outer Join  TTFACA_OPCI T2  
 On T1.CO_EMPR = T2.CO_EMPR  
 And T2.CO_MODU  = 'TEEM0001' 
 And T2.CO_MENU  = @ISCO_MENU              
 Where  T1.CO_EMPR  = @ISCO_EMPR         
   
        
--- *** DECLARACION DE VARIABLES PARA EL CURSOR CU_TTARUB_FINA_Q01_1 *** ---        
 Declare CU_TTARUB_FINA_Q01_1 CURSOR FOR        
 Select CO_EMPR, CO_SUBR, DE_SUBR        
 From  TTSUBR_FINA
 Where CO_EMPR = @ISCO_EMPR And
	   ST_REPO = 'S'       And
	   ST_PROY = 'S'
 Order By  NU_ORDE        
        
        
--- *** OBTINE EL NUMERO DE DIAS PARA GENERAR DIAS EN BLANCO VS RUBRO*** ---        
 Select  @VNNU_DIAS =  DateDiff ( Day, @IDFE_PRIM_DIAS, DateAdd(Day, -1, DateAdd(Month, 1, @IDFE_PRIM_DIAS))) + 1 ,        
   @VNNU_DIAS_COUNT = 1        
        
 select  @VDFE_DIAS_AUXI = @IDFE_PRIM_DIAS        
        
        
--- *** GENERAMOS A¾O Y MES DE LA OPERACION  *** ---        
 Select  @VNNU_ANNO =  Year(@IDFE_PROY),        
   @VNNU_MESE = Month(@IDFE_PROY)        
        
--- *** OBTINE EL MAXIMO DIA DEL MES ANTERIOR PARA LOS SALDOS DE LOS RUBRO*** ---        
 Select @VDFE_DIUL_ANTE = Max(FE_PROY_EJEC)        
 From  TDSUBR_PROY        
 Where CO_EMPR = @ISCO_EMPR And         
   NU_ANNO = DatePart(Year,DateAdd(Day, -1, @IDFE_PRIM_DIAS)) And         
   NU_MESE = DatePart(Month,DateAdd(Day, -1, @IDFE_PRIM_DIAS))        

 Select	@VNIM_DIGI = 0.00
        
--- *** OBTINE EL MAXIMO DIA ANTERIOR PROYECTADO DEL MES ACTUAL *** ---        
 Select @VDFE_DIAS_MESA = Max(FE_PROY_EJEC)        
 From  TDSUBR_PROY        
 Where CO_EMPR = @ISCO_EMPR And         
   NU_ANNO = @VNNU_ANNO And         
   NU_MESE = @VNNU_MESE And         
   FE_PROY_EJEC < @IDFE_PROY And     
   CO_MONE = @ISCO_MONE  
        
--- *** CURSOR GENERAR DIAS EN BLANCO CON CADA UNO DE LOS RUBROS *** ---        
 Open CU_TTARUB_FINA_Q01_1        
 Fetch CU_TTARUB_FINA_Q01_1 INTO @CSCO_EMPR, @CSCO_SUBR, @CSDE_SUBR        
 WHILE @@Fetch_status = 0        
 --- *** INICIO DE LA CAPTURA DE DATOS PARA CADA   A G R U P A M I E N T O  *** ---        
 Begin        
          
  WHILE @VNNU_DIAS_COUNT <= @VNNU_DIAS        
  BEGIN 
	Select	@VNIM_DIGI = IsNull(IM_DIGI,0.00)
	From	TDSUBR_PROY
	Where	CO_EMPR  = @CSCO_EMPR And 
			NU_ANNO  = @VNNU_ANNO And
			NU_MESE  = @VNNU_MESE And
			CO_SUBR  = @CSCO_SUBR And
			FE_PROY_EJEC = @IDFE_PROY And
		    CO_MONE = @ISCO_MONE And
			FE_DETA_DIGI = @VDFE_DIAS_AUXI 
	
   Insert Into #TDSUBR_PROY_DIAS        
    (CO_EMPR, NU_ANNO, NU_MESE, CO_SUBR, FE_PROY_EJEC,         
    CO_MONE, FE_DETA_DIGI,         
    DE_SUBR, IM_DIGI, IM_EJEC )        
   Values (@CSCO_EMPR, @VNNU_ANNO, @VNNU_MESE, @CSCO_SUBR, @IDFE_PROY,         
     @ISCO_MONE, @VDFE_DIAS_AUXI,         
     @CSDE_SUBR, @VNIM_DIGI, 0.00)        
        
   Select	@VDFE_DIAS_AUXI = DateAdd(Day, @VNNU_DIAS_COUNT, @IDFE_PRIM_DIAS ),
			@VNIM_DIGI = 0.00
        
   Select @VNNU_DIAS_COUNT = @VNNU_DIAS_COUNT + 1        
  END        
        
  ---  ---          
  --- *** REINICIALIZAMOS LAS VARIABLES    *** ---        
  Select  @VDFE_DIAS_AUXI = @IDFE_PRIM_DIAS,        
    @VNNU_DIAS_COUNT = 1        
        
        
  Fetch CU_TTARUB_FINA_Q01_1 INTO @CSCO_EMPR, @CSCO_SUBR, @CSDE_SUBR        
 End        
 Close CU_TTARUB_FINA_Q01_1         
 Deallocate CU_TTARUB_FINA_Q01_1         
               
--- *** INSERTAMOS LA TABLA TEMPORAL DE DIAS... *** ---        
  Insert Into #TDSUBR_PROY        
   (CO_EMPR, NU_ANNO, NU_MESE, CO_SUBR, FE_PROY_EJEC, CO_MONE, FE_DETA_DIGI, ST_DIGI_PROY,       
   IM_DIGI, IM_EJEC )        
  Select         
   CO_EMPR, NU_ANNO, NU_MESE, CO_SUBR, FE_PROY_EJEC,         
    CO_MONE, FE_DETA_DIGI, '¾', ---'P',        
    IM_DIGI, IM_EJEC        
  From  #TDSUBR_PROY_DIAS        
  Where CO_EMPR = @ISCO_EMPR And         
    NU_ANNO = @VNNU_ANNO And         
    NU_MESE = @VNNU_MESE And          
    FE_PROY_EJEC = @IDFE_PROY           
  And Not Exists ( Select #TDSUBR_PROY.CO_EMPR        
     From  #TDSUBR_PROY        
     Where #TDSUBR_PROY.CO_EMPR = #TDSUBR_PROY_DIAS.CO_EMPR And         
       #TDSUBR_PROY.NU_ANNO = #TDSUBR_PROY_DIAS.NU_ANNO And         
       #TDSUBR_PROY.NU_MESE = #TDSUBR_PROY_DIAS.NU_MESE And         
       #TDSUBR_PROY.CO_SUBR = #TDSUBR_PROY_DIAS.CO_SUBR And         
       #TDSUBR_PROY.FE_PROY_EJEC = #TDSUBR_PROY_DIAS.FE_PROY_EJEC And         
       #TDSUBR_PROY.FE_DETA_DIGI = #TDSUBR_PROY_DIAS.FE_DETA_DIGI  )        
        

-- Hago UPDATE a la #TDSUBR_PROY del campo TI_REPO        
Update #TDSUBR_PROY        
Set    TI_REPO = @ISTI_REPO        
        
        
--- *** ACTULALIZAMOS SALDOS A LA TABLA DE TRABAJO (BACKUP)   *** ---        
  Update #TDSUBR_PROY        
  Set        
		#TDSUBR_PROY.IM_SALD = (        
							  Select Sum(IsNull(TDSUBR_PROY.IM_DIGI,0) + 
IsNull(TDSUBR_PROY.IM_EJEC,0))        
							  From  TDSUBR_PROY,        
								   (        
								   Select CO_EMPR CO_EMPR, NU_ANNO NU_ANNO,        
								     NU_MESE NU_MESE, CO_SUBR CO_SUBR,        
								     Max(FE_PROY_EJEC) FE_PROY_EJEC        
								   From  TDSUBR_PROY        
								   Where CO_EMPR  = @ISCO_EMPR And        
								         NU_ANNO <= DatePart(Year ,DateAdd(Day, -1, 
@IDFE_PRIM_DIAS)) And
								         NU_MESE <= DatePart(Month,DateAdd(Day, -1, 
@IDFE_PRIM_DIAS))
								   Group By  CO_EMPR, NU_ANNO, NU_MESE, CO_SUBR        
								   ) T3  --- Tabla de Fechas Maximas Para los Saldos        
							Where TDSUBR_PROY.CO_EMPR = #TDSUBR_PROY.CO_EMPR And        
							TDSUBR_PROY.CO_SUBR = #TDSUBR_PROY.CO_SUBR And        
							TDSUBR_PROY.CO_EMPR = T3.CO_EMPR And
							TDSUBR_PROY.NU_ANNO = T3.NU_ANNO And
							TDSUBR_PROY.NU_MESE = T3.NU_MESE And
							TDSUBR_PROY.CO_SUBR = T3.CO_SUBR And
							TDSUBR_PROY.FE_PROY_EJEC = T3.FE_PROY_EJEC AND
							TDSUBR_PROY.TI_REPO = #TDSUBR_PROY.TI_REPO AND
							TDSUBR_PROY.CO_MONE = #TDSUBR_PROY.CO_MONE)
        
  Where #TDSUBR_PROY.CO_EMPR = @ISCO_EMPR And        
   #TDSUBR_PROY.NU_ANNO = @VNNU_ANNO And        
   #TDSUBR_PROY.NU_MESE = @VNNU_MESE And        
   #TDSUBR_PROY.FE_PROY_EJEC = @IDFE_PROY And        
        
               #TDSUBR_PROY.TI_REPO = @ISTI_REPO AND       
               #TDSUBR_PROY.CO_MONE = @ISCO_MONE AND         
        
   Exists (        
      Select TDSUBR_PROY.CO_EMPR        
      From  TDSUBR_PROY,        
       (        
       Select CO_EMPR CO_EMPR, NU_ANNO NU_ANNO,        
         NU_MESE NU_MESE, CO_SUBR CO_SUBR,        
         Max(FE_PROY_EJEC) FE_PROY_EJEC        
       From  TDSUBR_PROY        
       Group By  CO_EMPR, NU_ANNO, NU_MESE, CO_SUBR        
       ) T3   --- Tabla de Fechas Maximas Para los Saldos        
      Where TDSUBR_PROY.CO_EMPR = #TDSUBR_PROY.CO_EMPR And        
       TDSUBR_PROY.CO_SUBR = #TDSUBR_PROY.CO_SUBR And        
       TDSUBR_PROY.CO_EMPR = T3.CO_EMPR And        
       TDSUBR_PROY.NU_ANNO = T3.NU_ANNO And        
       TDSUBR_PROY.NU_MESE = T3.NU_MESE And        
       TDSUBR_PROY.CO_SUBR = T3.CO_SUBR And        
       TDSUBR_PROY.FE_PROY_EJEC = T3.FE_PROY_EJEC AND        
        
                                   TDSUBR_PROY.TI_REPO = #TDSUBR_PROY.TI_REPO AND        
                                   TDSUBR_PROY.CO_MONE = #TDSUBR_PROY.CO_MONE)        
        
        
        
        
        
        
--- *************************************************** ---        
--- *** EJECUTAMOS EL SELECT QUE CARGARA LA TABLA   *** ---        
--- *************************************************** ---        
        
Select #TDSUBR_PROY.CO_SUBR,        
  TTSUBR_FINA.DE_SUBR, #TDSUBR_PROY.FE_PROY_EJEC,         
  Max(#TDSUBR_PROY.ST_DIGI_PROY),        
  Max(IsNull(#TDSUBR_PROY.IM_SALD,0)) AS IM_SALD_ANTE,        
  Sum ( PatIndex( '1', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '2', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '3', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
 ) ,        
  Sum ( PatIndex( '4', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '5', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))      
   ) ,        
  Sum ( PatIndex( '6', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '7', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '8', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '9', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
  ) ,        
  Sum ( PatIndex( '10', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '11', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '12', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '13', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '14', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '15', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '16', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '17', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '18', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '19', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '20', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '21', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '22', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '23', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '24', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '25', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '26', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '27', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '28', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '29'      
, RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '30', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   ) ,        
  Sum ( PatIndex( '31', RTrim(Ltrim(Convert(Varchar(2),IsNull(DatePart (day, FE_DETA_DIGI),0)))) )*         
   (IsNull(IM_EJEC,0)+ IsNull(IM_DIGI,0))        
   )         
From  #TDSUBR_PROY , TTSUBR_FINA         
Where #TDSUBR_PROY.CO_EMPR = @ISCO_EMPR And         
  #TDSUBR_PROY.NU_ANNO = @VNNU_ANNO And         
  #TDSUBR_PROY.NU_MESE = @VNNU_MESE And          
  #TDSUBR_PROY.FE_PROY_EJEC = @IDFE_PROY And         
  #TDSUBR_PROY.CO_EMPR = TTSUBR_FINA.CO_EMPR And        
  #TDSUBR_PROY.CO_SUBR = TTSUBR_FINA.CO_SUBR         
Group BY  #TDSUBR_PROY.CO_SUBR, TTSUBR_FINA.DE_SUBR, #TDSUBR_PROY.FE_PROY_EJEC, #TDSUBR_PROY.TI_REPO        
Order By  Max(TTSUBR_FINA.NU_ORDE)        
        
End        
/*-------------------------------------------------- F I N --------------------------------------------------*/


GO
